﻿Imports System.Data.SqlClient

Public Class frmRenewalsStatus

    Sub FillRenewal()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select SNo,Status,ClientID,ClientName,InsuranceCompany,PolicyNo,ClassofBusiness," & _
                                      "IssueDate,ExpiryDate,IsNull(AssignedTo,N'') AssignedTo,IsNull(AssignedBy,N'') AssignedBy," & _
                                      "AssignedDate,OrginalSNo From Renewal Where Status In (N'Active',N'Not Active') Order By ExpiryDate", cnn)
            Dim Reader As SqlDataReader
            Dim AssignDate As String

            Me.GridRenewals.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                If Reader.Item("AssignedDate").ToString = "1/1/1900 12:00:00 AM" Then
                    AssignDate = ""
                ElseIf Reader.Item("AssignedDate").ToString = "" Then
                    AssignDate = ""
                Else
                    AssignDate = CDate(Reader.Item("AssignedDate")).ToString("yyyy/MM/dd")
                End If

                Me.GridRenewals.Rows.Add(New String() {Reader.Item("SNo"), "Update", Reader.Item("Status"), Reader.Item("ClientID"), Reader.Item("ClientName"), _
                                                       Reader.Item("InsuranceCompany"), Reader.Item("PolicyNo"), _
                                                       Reader.Item("ClassofBusiness"), CDate(Reader.Item("IssueDate")).ToString("yyyy/MM/dd"), _
                                                       CDate(Reader.Item("ExpiryDate")).ToString("yyyy/MM/dd"), _
                                                       Reader.Item("AssignedTo"), Reader.Item("AssignedBy"), AssignDate, Reader.Item("OrginalSNo")})
            End While
            cnn.Close()

            'Check Colors
            For Each row As DataGridViewRow In Me.GridRenewals.Rows
                If row.Cells(2).Value = "Active" Then
                    row.Cells(2).Style.BackColor = Color.Yellow

                ElseIf row.Cells(2).Value = "Not Active" Then
                    row.Cells(2).Style.BackColor = Color.Magenta
                End If
            Next

            'Check Expiry Date
            For Each Row As DataGridViewRow In Me.GridRenewals.Rows
                If Now.Date >= CType(Row.Cells(9).Value, Date).Date Then
                    Row.Cells(9).Style.BackColor = Color.Red
                Else
                    Row.Cells(9).Style.BackColor = Color.White
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmRenewalsStatus_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        FillRenewal()
    End Sub

    Private Sub GridRenewals_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridRenewals.CellClick
        If e.ColumnIndex = 1 Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim a As New frmRenewalsUpdate
                a.FillInsuranceCompanies()
                a.FillClassofBusiness()
                a.SNo = CInt(Me.GridRenewals.Rows(e.RowIndex).Cells(0).Value)
                a.OriginalSNo = CInt(Me.GridRenewals.Rows(e.RowIndex).Cells(13).Value)
                a.ShowDialog()

                FillRenewal()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub
End Class